﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Configuration;
using System.Data;
using System.Data.OleDb;

public class SampleHelper
{

	public static int QueryValue ( string commandText )
	{
		SampleDS dataSet = ExecuteQuery ( commandText, "Query" );

		try
		{ return Convert.ToInt32 ( dataSet.Query[0][0] ); }
		catch
		{ return -1; }

	}

	public static SampleDS ExecuteQuery ( string commandText, string tableName )
	{ return ExecuteQuery ( commandText, null, tableName ); }

	public static SampleDS ExecuteQuery ( string commandText, SampleDS sampleSet, string tableName )
	{
		OleDbConnection connection = new OleDbConnection ( WebConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString );
		OleDbCommand command = new OleDbCommand ( commandText, connection );
		OleDbDataAdapter adapter = new OleDbDataAdapter ( command );

		if ( null == sampleSet )
			sampleSet = new SampleDS ( );

		try
		{

			if ( string.IsNullOrEmpty ( tableName ) )
				adapter.Fill ( sampleSet );
			else
				adapter.Fill ( sampleSet.Tables[tableName] );

		}
		finally
		{

			if ( connection.State == ConnectionState.Open )
				connection.Close ( );

		}

		return sampleSet;
	}

	public static DataTable Filter ( string filter, string sort, int beginIndex, int endIndex, string tableName, out int count  )
	{ return Filter ( filter, sort, beginIndex, endIndex, null, tableName, out count ); }

	public static DataTable Filter ( string filter, string sort, int beginIndex, int endIndex, SampleDS sampleSet, string tableName, out int count )
	{

		if ( null == sampleSet )
		{
			sampleSet = new SampleDS ( );
			sampleSet.ReadXml ( HttpContext.Current.Server.MapPath ( "~/App_Data/Sample.xml" ) );
		}

		DataView view;

		if ( string.IsNullOrEmpty ( tableName ) )
			view = new DataView ( sampleSet.Tables[0] );
		else
			view = new DataView ( sampleSet.Tables[tableName] );


		if ( !view.Table.Columns.Contains ( "RN" ) )
			view.Table.Columns.Add ( "RN", typeof ( int ) );

		view.RowFilter = filter;
		count = view.Count;
		view.Sort = sort;
		int index = 1;

		foreach ( DataRowView rowView in view )
			rowView["RN"] = index++;

		if ( string.IsNullOrEmpty ( view.RowFilter ) )
			view.RowFilter = string.Format ( "RN >= {0} and RN <= {1}", beginIndex, endIndex );
		else
			view.RowFilter = string.Format ( "{2} and RN >= {0} and RN <= {1}", beginIndex, endIndex, filter );

		return view.ToTable ( );
	}

	public static List<object> CreateJSONArray ( DataTable table )
	{
		List<object> jsonRows = new List<object> ( );

		if ( null != table )
			foreach ( DataRow row in table.Rows )
			{
				SortedDictionary<string, object> jsonRow = new SortedDictionary<string, object> ( );

				foreach ( DataColumn column in table.Columns )
					jsonRow.Add ( column.ColumnName.ToLower ( ), row[column] );

				jsonRows.Add ( jsonRow );
			}

		return CreateJSONArray ( jsonRows.ToArray ( ) );
	}

	public static SortedDictionary<string, object> CreateJSONObject ( KeyValuePair<string, object>[] pairs )
	{
		SortedDictionary<string, object> json = new SortedDictionary<string, object> ( );

		if ( null != pairs )
			foreach ( KeyValuePair<string, object> pair in pairs )
				if ( !string.IsNullOrEmpty ( pair.Key ) )
					json.Add ( pair.Key, pair.Value );

		return json;
	}

	public static List<object> CreateJSONArray ( object[] items )
	{
		List<object> json = new List<object> ( );

		if ( null != items )
			json.AddRange ( items );

		return json;
	}

}